{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  mysql-connector 驱动"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection.MySQLConnection object at 0x0000028A6C275630>\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",       # 数据库主机地址\n",
    "  user=\"root\",    # 数据库用户名\n",
    "  passwd=\"123456\",   # 数据库密码\n",
    "  auth_plugin='mysql_native_password'\n",
    ")\n",
    " \n",
    "print(mydb)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "\n",
    "创建数据库\n",
    "\n",
    "创建数据库使用 \"CREATE DATABASE\" 语句，以下创建一个名为 runoob_db 的数据库：\n",
    "\n",
    "\n",
    "'''\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute('create database ruuoob_db2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('ruuoob_db',)\n",
      "('ruuoob_db2',)\n",
      "('sys',)\n",
      "('test',)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "mycursor.execute(\"SHOW DATABASES\")\n",
    " \n",
    "for x in mycursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建数据表\n",
    "import mysql.connector\n",
    "\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",       # 数据库主机地址\n",
    "  user=\"root\",    # 数据库用户名\n",
    "  passwd=\"123456\",   # 数据库密码\n",
    "  auth_plugin='mysql_native_password',\n",
    "  database=\"ruuoob_db2\"\n",
    ")\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "sqlCreateTableSites = 'create table sites3(name VARCHAR(255), url VARCHAR(255))'\n",
    "\n",
    "mycursor.execute(sqlCreateTableSites)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('sites',)\n",
      "('sites2',)\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"SHOW TABLES\")\n",
    "for x in mycursor:\n",
    "    print(x)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "\n",
    "主键设置\n",
    "创建表的时候我们一般都会设置一个主键（PRIMARY KEY），我们可以使用 \"INT AUTO_INCREMENT PRIMARY KEY\" 语句来创建一个主键，主键起始值为 1，逐步递增。\n",
    "\n",
    "如果我们的表已经创建，我们需要使用 ALTER TABLE 来给表添加主键：\n",
    "\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "mycursor.execute('alter table sites2 add column id int auto_increment primary key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 插入成功\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "插入数据\n",
    "插入数据使用 \"INSERT INTO\" 语句：\n",
    "\n",
    "'''\n",
    "sqlStr = 'insert into sites2(name,url) values (%s,%s)'\n",
    "val = ('android','https://developer.android.com/about/versions/')\n",
    "mycursor.execute(sqlStr,val)\n",
    "mydb.commit()                     # 数据表内容有更新，必须使用到该语句\n",
    "print(mycursor.rowcount,'插入成功')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 批量插入成功\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "批量插入\n",
    "批量插入使用 executemany() 方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据：\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = 'insert into sites2(name,url) values (%s,%s)'\n",
    "val = [\n",
    "    ('菜鸟教程','https://www.runoob.com/python3/'),\n",
    "    ('百度','https://www.baidu.com'),\n",
    "    ('firebase','https://firebase.google.com/docs/ml-kit/android/detect-faces')\n",
    "]\n",
    "\n",
    "mycursor.executemany(sqlStr,val)\n",
    "mydb.commit() # 数据表内容有更新，必须使用到该语句\n",
    "print(mycursor.rowcount,'批量插入成功')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('android', 'https://developer.android.com/about/versions/', 1)\n",
      "('菜鸟教程', 'https://www.runoob.com/python3/', 2)\n",
      "('百度', 'https://www.baidu.com', 3)\n",
      "('firebase', 'https://firebase.google.com/docs/ml-kit/android/detect-faces', 4)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "查询数据\n",
    "查询数据使用 SELECT 语句：\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "sqlStr = 'select * from sites2'\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall();# fetchall() 获取所有记录\n",
    "\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('android', 'https://developer.android.com/about/versions/', 1)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "如果我们只想读取一条数据，可以使用 fetchone() 方法：\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = 'select * from sites2'\n",
    "mycursor.execute(sqlStr)\n",
    "myOneResult = mycursor.fetchone()\n",
    "print(myOneResult)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('android', 'https://developer.android.com/about/versions/', 1)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "where 条件语句\n",
    "如果我们要读取指定条件的数据，可以使用 where 语句：\n",
    "\n",
    "'''\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",       # 数据库主机地址\n",
    "  user=\"root\",    # 数据库用户名\n",
    "  passwd=\"123456\",   # 数据库密码\n",
    "  auth_plugin='mysql_native_password',\n",
    "  database=\"ruuoob_db2\"\n",
    ")\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "sqlStr = \" select * from sites2 where name = 'android' \"\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall()\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('firebase', 'https://firebase.google.com/docs/ml-kit/android/detect-faces', 4)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "  \n",
    "  使用通配符 %：\n",
    "  \n",
    "'''\n",
    "\n",
    "sqlStr = \"select * from sites2 where name like '%fire%'\"\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall()\n",
    "\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('android', 'https://developer.android.com/about/versions/', 1)\n",
      "('firebase', 'https://firebase.google.com/docs/ml-kit/android/detect-faces', 4)\n",
      "('百度', 'https://www.baidu.com', 3)\n",
      "('菜鸟教程', 'https://www.runoob.com/python3/', 2)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "排序\n",
    "查询结果排序可以使用 ORDER BY 语句，默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC。\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "sqlStr = 'select * from sites2 order by name asc'\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall()\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('android', 'https://developer.android.com/about/versions/', 1)\n",
      "('菜鸟教程', 'https://www.runoob.com/python3/', 2)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "Limit\n",
    "如果我们要设置查询的数据量，可以通过 \"LIMIT\" 语句来指定\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = 'select * from sites2 limit 2'\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall()\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('菜鸟教程', 'https://www.runoob.com/python3/', 2)\n",
      "('百度', 'https://www.baidu.com', 3)\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "指定起始位置，使用的关键字是 OFFSET：\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = 'select * from sites2 limit 2 offset 1'  # 0 为 第一条，1 为第二条，以此类推\n",
    "mycursor.execute(sqlStr)\n",
    "myResult = mycursor.fetchall()\n",
    "for x in myResult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 删除成功\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "删除记录\n",
    "删除记录使用 \"DELETE FROM\" 语句：\n",
    "\n",
    "\n",
    "\n",
    "注意：要慎重使用删除语句，删除语句要确保指定了 WHERE 条件语句，否则会导致整表数据被删除。\n",
    "\n",
    "为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义删除语句的条件：\n",
    "\n",
    "\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = \"delete from sites2 where name = %s\"\n",
    "name = ('百度',)\n",
    "mycursor.execute(sqlStr,name)\n",
    "mydb.commit()\n",
    "print(mycursor.rowcount,'删除成功')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 更新成功\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "\n",
    "更新表数据\n",
    "数据表更新使用 \"UPDATE\" 语句：\n",
    "\n",
    "\n",
    "\n",
    "注意：UPDATE 语句要确保指定了 WHERE 条件语句，否则会导致整表数据被更新。\n",
    "\n",
    "为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义更新语句的条件：\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = \"update sites2 set name = %s where name = %s \"\n",
    "val = ('学习python','菜鸟教程')\n",
    "mycursor.execute(sqlStr,val)\n",
    "mydb.commit()\n",
    "print(mycursor.rowcount,'更新成功')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "\n",
    "删除表\n",
    "删除表使用 \"DROP TABLE\" 语句， IF EXISTS 关键字是用于判断表是否存在，只有在存在的情况才删除：\n",
    "\n",
    "'''\n",
    "\n",
    "sqlStr = 'drop table if exists sites' # 删除数据表 sites\n",
    "mycursor.execute(sqlStr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
